package com.lvmama.rhino.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.lvmama.rhino.entity.SendMailVO;
import com.lvmama.rhino.enums.EnumForCategory;
import com.lvmama.rhino.enums.NPSPushEmailListEnum;
import com.lvmama.rhino.enums.OrderTypeEnum;
import com.lvmama.rhino.mapperNps.NPSMapper;
import com.lvmama.rhino.pojo.NPSCommit;
import com.lvmama.rhino.pojo.NPSCommitQuery;
import com.lvmama.rhino.pojo.NPSStatistics;
import com.lvmama.rhino.service.NPSService;
import com.lvmama.rhino.utils.DateUtil;
import com.lvmama.rhino.utils.SendMailUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * @Author:caoyong
 * @Description:
 * @Date：2017/10/18
 */
@Service("nPSService")
public class NPSServiceImpl implements NPSService {
    private Logger log = Logger.getLogger(NPSServiceImpl.class);
    public static ExecutorService executos = Executors.newFixedThreadPool(5);

    @Autowired
    private NPSMapper npsMapper;
    private final String[] EXPORT_NPS_COLUMN = new String[]{"序号", "编号", "提交时间", "会员ID",
            "手机号", "品类", "订单ID", "提交时状态", "分值", "备注"};

    /**
     * 查询提交的nps问卷
     *
     * @param query 查询条件
     * @return 结果分页
     */
    @Override
    public PageInfo<NPSCommit> queryNPSCommitPage(NPSCommitQuery query) {
        log.info("queryNPSCommitPage start.");
        //设置分页
        PageHelper.startPage(query.getCurrentPage(), query.getPageSize());
        PageHelper.orderBy(query.getOrderBy());
        List<NPSCommit> npsCommits = null;
        try {
            npsCommits = queryNPSCommitList(query);
        } catch (Exception e) {
            log.error("queryNPSCommitPage error:" + e.getMessage(), e);
        }
        log.info("queryNPSCommitPage end.");
        return new PageInfo<>(npsCommits);
    }

    /**
     * 查询提交的nps内部问卷
     *
     * @param query 查询条件
     * @return 结果分页
     */
    @Override
    public PageInfo<NPSCommit> queryNPSInternalCommitPage(NPSCommitQuery query) {
        log.info("queryNPSInternalCommitPage start.");
        //设置分页
        PageHelper.startPage(query.getCurrentPage(), query.getPageSize());
        PageHelper.orderBy(query.getOrderBy());
        List<NPSCommit> npsCommits = null;
        try {
            npsCommits = queryNPSInternalCommitList(query);
        } catch (Exception e) {
            log.error("queryNPSInternalCommitPage error:" + e.getMessage(), e);
        }
        log.info("queryNPSInternalCommitPage end.");
        return new PageInfo<>(npsCommits);
    }

    /**
     * 查询提交的nps问卷
     *
     * @param query 查询条件
     * @return 结果集
     */
    @Override
    public List<NPSCommit> queryNPSCommitList(NPSCommitQuery query) {
        log.info("queryNPSCommitList start.");
        List<NPSCommit> npsCommits = null;
        try {
            npsCommits = npsMapper.queryNPSCommitList(query);
            npsCommitConverter(npsCommits, query.getNpsQuestionType());
        } catch (Exception e) {
            log.error("queryNPSCommitList error:" + e.getMessage(), e);
        }
        log.info("queryNPSCommitList end.");
        return npsCommits;
    }

    @Override
    public List<NPSCommit> queryNPSInternalCommitList(NPSCommitQuery query) {
        log.info("queryNPSInternalCommitList start.");
        List<NPSCommit> npsCommits = null;
        try {
            npsCommits = npsMapper.queryNPSInternalCommitList(query);
            npsCommitConverter(npsCommits, query.getNpsQuestionType());
        } catch (Exception e) {
            log.error("queryNPSInternalCommitList error:" + e.getMessage(), e);
        }
        log.info("queryNPSInternalCommitList end.");
        return npsCommits;
    }

    @Override
    public List<NPSStatistics> queryNpsStatistics(NPSCommitQuery query) {
        log.info("queryNpsStatistics start.");
        List<NPSStatistics> npsStatistics = null;
        try {
            npsStatistics = npsMapper.queryNpsStatistics(query);
            if (npsStatistics != null && !npsStatistics.isEmpty()) {
                for (NPSStatistics npsStat : npsStatistics){
                    if(query.isGroupByCategory()){
                        npsStat.setCategoryName(EnumForCategory.getCategoryShowNameByValue(npsStat.getCategory()));
                    }
                    if(query.isGroupCommitType()){
                        OrderTypeEnum orderTypeEnum = OrderTypeEnum.getEnum(npsStat.getCommitType());
                        npsStat.setCommitTypeName(orderTypeEnum==null? npsStat.getCommitType() : orderTypeEnum.getShowName());
                    }
                    if(null == npsStat.getNps()){
                        npsStat.setNps(0F);
                    }
                }
            }
            //添加总计
            if((query.isGroupByCategory() && null != npsStatistics && npsStatistics.size()>1) || query.isGroupCommitType()){
                query.setGroupByCategory(false);
                query.setGroupCommitType(false);
                NPSStatistics npsStatis = npsMapper.queryNpsStatistics(query).get(0);
                npsStatis.setCategoryName("总计");
                npsStatis.setCommitTypeName("总计");
                if(npsStatistics!=null){
                    npsStatistics.add(npsStatis);
                }
            }
        } catch (Exception e) {
            log.error("queryNpsStatistics error:" + e.getMessage(), e);
        }
        log.info("queryNpsStatistics end.");
        return npsStatistics;
    }

    @Override
    public NPSStatistics queryNpsInternalStatistics(NPSCommitQuery query) {
        log.info("queryNpsInternalStatistics start.");
        NPSStatistics npsStatistics = null;
        try {
            npsStatistics = npsMapper.queryNpsInternalStatistics(query);
            if (npsStatistics != null && npsStatistics.getNps() == null) {
                npsStatistics.setNps(0F);
            }
        } catch (Exception e) {
            log.error("queryNpsInternalStatistics error:" + e.getMessage(), e);
        }
        log.info("queryNpsInternalStatistics end.");
        return npsStatistics;
    }

    private void npsCommitConverter(List<NPSCommit> npsCommits, String npsQuestionType) {
        for (NPSCommit commit : npsCommits) {
            commit.setCommitTypeCode(commit.getCommitType());
            //枚举转换
            commit.setCategoryName(EnumForCategory.getCategoryShowNameByValue
                    (String.valueOf(commit.getCategoryId())));
            //时期转换
            commit.setCommitTime(DateUtil.dateFormat(commit.getCreateTime(),
                    DateUtil.PATTERN_YYYY_MM_DD_HHMMSS));
            if (commit.getScore() != null) {
                String socreStyle = null;
                if (commit.getScore() < 7) {
                    socreStyle = "<div style=\"background-color: #666666;" +
                            "width: 100%;height: 22px;color: #fff;\">" + commit.getScore() + "</div>";
                } else if (commit.getScore() > 6 && commit.getScore() < 9) {
                    socreStyle = "<div style=\"background-color: #5CA2F8;" +
                            "width: 100%;height: 22px;color: #fff;\">" + commit.getScore() + "</div>";
                } else if (commit.getScore() > 8) {
                    socreStyle = "<div style=\"background-color: #FE3C71;" +
                            "width: 100%;height: 22px;color: #fff;\">" + commit.getScore() + "</div>";
                }
                commit.setScoreStyle(socreStyle);
            }

            OrderTypeEnum orderTypeEnum = OrderTypeEnum.getEnum(commit.getCommitType());
            if (null != orderTypeEnum) {
                commit.setCommitType(orderTypeEnum.getShowName());
            } else {
                commit.setCommitType(commit.getCommitType()==null ? "" : commit.getCommitType());
            }
            if (null == commit.getUserId()) {
                commit.setUserId("");
            }
            if (null == commit.getPhoneNumber()) {
                commit.setPhoneNumber("");
            }
            if (null == commit.getOrderId()) {
                commit.setOrderId("");
            }
            if (null == commit.getSurveyNo()) {
                commit.setSurveyNo("");
            }
            commit.setNpsQuestionType(npsQuestionType);
            commit.setOriginRemark(commit.getRemark());
            if (StringUtils.isNotEmpty(commit.getRemark()) && commit.getRemark().length() > 30) {
                String remark = commit.getRemark().substring(0, 30) + "<a title='" + commit.getRemark()
                        + "' data-rel='tooltip' href='javascript:void(0)' class='tooltip-info'>...</a>";
                commit.setRemark(remark);
            } else {
                commit.setRemark(commit.getRemark() == null ? "" : commit.getRemark());
            }
        }
    }

    @Override
    public InputStream getNPSExcelInputStream(List<NPSCommit> rows) {
        log.info("getNPSExcelInputStream start.");
        try {
            // 声明一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();

            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();
            sheet.setColumnWidth(0, 256 * 10);
            sheet.setColumnWidth(1, 256 * 40);
            sheet.setColumnWidth(2, 256 * 25);
            sheet.setColumnWidth(3, 256 * 40);
            sheet.setColumnWidth(4, 256 * 20);
            sheet.setColumnWidth(5, 256 * 10);
            sheet.setColumnWidth(6, 256 * 10);
            sheet.setColumnWidth(7, 256 * 15);
            sheet.setColumnWidth(8, 256 * 5);
            sheet.setColumnWidth(9, 256 * 50);

            // 标题样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置标题样式
            style.setFillForegroundColor(HSSFColor.TAN.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 生成标题字体
            HSSFFont font = workbook.createFont();
            font.setFontName("微软雅黑");
            font.setColor(HSSFColor.BLACK.index);
            font.setFontHeightInPoints((short) 12);
            // 把字体应用到当前的样式
            style.setFont(font);

            // 数字显示格式
            HSSFCellStyle styleNum = workbook.createCellStyle();
            styleNum.setFillForegroundColor(HSSFColor.WHITE.index);
            styleNum.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            styleNum.setAlignment(CellStyle.ALIGN_RIGHT);
            styleNum.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            styleNum.setWrapText(true);

            // 生成内容样式-贬低者
            HSSFCellStyle style2 = workbook.createCellStyle();
            style2.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style2.setAlignment(CellStyle.ALIGN_CENTER);
            style2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style2.setWrapText(true);
            // 生成内容字体
            HSSFFont font2 = workbook.createFont();
            font2.setFontName("宋体");
            font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
            font2.setFontHeightInPoints((short) 12);
            // 把字体应用到当前的样式
            style2.setFont(font2);

            // 生成内容样式-被动者
            HSSFCellStyle style3 = workbook.createCellStyle();
            style3.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
            style3.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style3.setAlignment(CellStyle.ALIGN_CENTER);
            style3.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style3.setWrapText(true);

            // 生成内容样式-推荐者
            HSSFCellStyle style4 = workbook.createCellStyle();
            style4.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
            style4.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            style4.setAlignment(CellStyle.ALIGN_CENTER);
            style4.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            style4.setWrapText(true);

            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            String[] headers = EXPORT_NPS_COLUMN;
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }

            // 查询无数据时在导出文档中做相应的提示
            if (rows.size() == 0) {
                row = sheet.createRow(1);
                HSSFFont font3 = workbook.createFont();
                font3.setFontName("宋体");
                font3.setFontHeightInPoints((short) 12);
                HSSFCell cell = row.createCell(0);
                cell.setCellStyle(style2);
                HSSFRichTextString richString = new HSSFRichTextString("1");
                richString.applyFont(font3);
                cell.setCellValue(richString);

                HSSFCell cell1 = row.createCell(1);
                cell1.setCellStyle(style2);
                richString = new HSSFRichTextString("查询无数据!");
                richString.applyFont(font3);
                cell1.setCellValue(richString);
            }
            // 遍历集合数据，产生数据行
            Iterator<NPSCommit> it = rows.iterator();
            int index = 0;

            while (it.hasNext()) {
                index++;
                row = sheet.createRow(index);
                NPSCommit t = it.next();

                if (t != null) {
                    HSSFCellStyle cellStyle;
                    if (t.getScore() > 7 && t.getScore() < 9) {
                        cellStyle = style3;
                    } else if (t.getScore() > 8) {
                        cellStyle = style4;
                    } else {
                        cellStyle = style2;
                    }
                    HSSFFont font3 = workbook.createFont();
                    font3.setFontName("宋体");
                    font3.setFontHeightInPoints((short) 12);
                    HSSFCell cellIndex = row.createCell(0);
                    cellIndex.setCellStyle(cellStyle);
                    HSSFRichTextString richString = new HSSFRichTextString(index + "");
                    richString.applyFont(font3);
                    cellIndex.setCellValue(richString);

                    HSSFCell cell1 = row.createCell(1);
                    cell1.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getId());
                    richString.applyFont(font3);
                    cell1.setCellValue(richString);

                    HSSFCell cell2 = row.createCell(2);
                    cell2.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getCommitTime());
                    richString.applyFont(font3);
                    cell2.setCellValue(richString);

                    HSSFCell cell3 = row.createCell(3);
                    cell3.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getUserId());
                    richString.applyFont(font3);
                    cell3.setCellValue(richString);

                    HSSFCell cell4 = row.createCell(4);
                    cell4.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getPhoneNumber());
                    richString.applyFont(font3);
                    cell4.setCellValue(richString);

                    HSSFCell cell5 = row.createCell(5);
                    cell5.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getCategoryName());
                    richString.applyFont(font3);
                    cell5.setCellValue(richString);

                    HSSFCell cell6 = row.createCell(6);
                    cell6.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getOrderId());
                    richString.applyFont(font3);
                    cell6.setCellValue(richString);

                    HSSFCell cell7 = row.createCell(7);
                    cell7.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getCommitType());
                    richString.applyFont(font3);
                    cell7.setCellValue(richString);

                    HSSFCell cell8 = row.createCell(8);
                    cell8.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(String.valueOf(t.getScore()));
                    richString.applyFont(font3);
                    cell8.setCellValue(richString);

                    HSSFCell cell9 = row.createCell(9);
                    cell9.setCellStyle(cellStyle);
                    richString = new HSSFRichTextString(t.getOriginRemark());
                    richString.applyFont(font3);
                    cell9.setCellValue(richString);
                }
            }
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            workbook.write(baos);
            baos.flush();
            byte[] bt = baos.toByteArray();
            InputStream is = new ByteArrayInputStream(bt, 0, bt.length);
            baos.close();
            return is;
        } catch (Exception e) {
            log.error("getNPSExcelInputStream error:" + e.getMessage(), e);
        }
        log.info("getNPSExcelInputStream end.");
        return null;
    }

    @Override
    public boolean sendNPSEmail() {
        boolean flag;
        log.info("sendNPSEmail start");
        try {
            Date[] period = DateUtil.getLastWeekPeriod();
            String[] periodStr = DateUtil.getLastWeekPeriodStr();
            //给各BU推送邮件
            for (NPSPushEmailListEnum bu : NPSPushEmailListEnum.getAllEnum()) {
                NPSCommitQuery query = new NPSCommitQuery();
                //获取上一周的开始结束时间
                query.setStartTime(period[0]);
                query.setEndTime(period[1]);
                query.setCategoryIds(bu.getCategoryIds());
                query.setGroupByCategory(true);
                List<NPSCommit> npsCommits = queryNPSCommitList(query);
                if (null != npsCommits && !npsCommits.isEmpty()) {
                    //查询出NPS统计信息
                    List<NPSStatistics> npsStatistics = queryNpsStatistics(query);
                    InputStream npsExcelInputStream = getNPSExcelInputStream(npsCommits);
                    final SendMailVO sendMailVO = new SendMailVO();
                    StringBuilder sb = new StringBuilder(periodStr[0] + "~" + periodStr[1] + "【" + bu.getName() + "】<br/>");
                    sb.append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"465\" style=\"border-collapse: collapse;width:465pt\">\n");
                    sb.append("\n" +
                            " <colgroup><col width=\"94\" style=\"mso-width-source:userset;mso-width-alt:4010;width:94pt\">\n" +
                            " <col width=\"86\" style=\"mso-width-source:userset;mso-width-alt:3669;width:86pt\">\n" +
                            " <col width=\"68\" span=\"3\" style=\"mso-width-source:userset;mso-width-alt:2901; width:68pt\">\n" +
                            " <col width=\"81\" style=\"mso-width-source:userset;mso-width-alt:3456;width:81pt\">\n" +
                            " </colgroup><tbody><tr height=\"20\" style=\"height:20.0pt\">\n" +
                            "  <td height=\"20\" width=\"94\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border: 1px solid white; background-color: rgb(250, 191, 143); height: 20pt; width: 94pt; background-position: initial initial; background-repeat: initial initial;\">　</td>\n" +
                            "  <td width=\"86\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 86pt; background-position: initial initial; background-repeat: initial initial;\">贬低者</td>\n" +
                            "  <td width=\"68\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 68pt; background-position: initial initial; background-repeat: initial initial;\">被动者</td>\n" +
                            "  <td width=\"68\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 68pt; background-position: initial initial; background-repeat: initial initial;\">推荐者</td>\n" +
                            "  <td width=\"68\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 68pt; background-position: initial initial; background-repeat: initial initial;\">总人数</td>\n" +
                            "  <td width=\"81\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 81pt; background-position: initial initial; background-repeat: initial initial;\">NPS</td>\n" +
                            " </tr>\n");
                    for (NPSStatistics statistics: npsStatistics){
                        //开始拼接表格内容
                        sb.append(" <tr height=\"20\" style=\"height:20.0pt\">\n");
                        sb.append("  <td height=\"20\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: white; border-bottom-color: white; border-left-color: white; background-color: rgb(250, 191, 143); height: 20pt; background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getCategoryName());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getDetractor());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getPassives());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getPresenter());
                        sb.append("</td>\n" );
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getTotal());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getNps());
                        sb.append("%</td></tr>");
                    }
                    sb.append("</tbody></table>");
                    sb.append("<br/><br/>");
                    //查询订单状态的另一表格
                    query.setGroupByCategory(false);
                    query.setGroupCommitType(true);
                    npsStatistics = queryNpsStatistics(query);
                    sb.append("<table border=\"0\" cellpadding=\"0\" cellspacing=\"0\" width=\"465\" style=\"border-collapse: collapse;width:465pt\">\n");
                    sb.append("\n" +
                            " <colgroup><col width=\"94\" style=\"mso-width-source:userset;mso-width-alt:4010;width:94pt\">\n" +
                            " <col width=\"86\" style=\"mso-width-source:userset;mso-width-alt:3669;width:86pt\">\n" +
                            " <col width=\"68\" span=\"3\" style=\"mso-width-source:userset;mso-width-alt:2901; width:68pt\">\n" +
                            " <col width=\"81\" style=\"mso-width-source:userset;mso-width-alt:3456;width:81pt\">\n" +
                            " </colgroup><tbody><tr height=\"20\" style=\"height:20.0pt\">\n" +
                            "  <td height=\"20\" width=\"94\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border: 1px solid white; background-color: rgb(250, 191, 143); height: 20pt; width: 94pt; background-position: initial initial; background-repeat: initial initial;\">　</td>\n" +
                            "  <td width=\"86\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 86pt; background-position: initial initial; background-repeat: initial initial;\">贬低者</td>\n" +
                            "  <td width=\"68\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 68pt; background-position: initial initial; background-repeat: initial initial;\">被动者</td>\n" +
                            "  <td width=\"68\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 68pt; background-position: initial initial; background-repeat: initial initial;\">推荐者</td>\n" +
                            "  <td width=\"68\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 68pt; background-position: initial initial; background-repeat: initial initial;\">总人数</td>\n" +
                            "  <td width=\"81\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; text-align: center; border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-style: solid solid solid none; border-top-color: white; border-right-color: white; border-bottom-color: white; background-color: rgb(250, 191, 143); width: 81pt; background-position: initial initial; background-repeat: initial initial;\">NPS</td>\n" +
                            " </tr>\n");
                    for (NPSStatistics statistics: npsStatistics){
                        //开始拼接表格内容
                        sb.append(" <tr height=\"20\" style=\"height:20.0pt\">\n");
                        sb.append("  <td height=\"20\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-style: none solid solid; border-right-color: white; border-bottom-color: white; border-left-color: white; background-color: rgb(250, 191, 143); height: 20pt; background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getCommitTypeName());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getDetractor());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getPassives());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getPresenter());
                        sb.append("</td>\n" );
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getTotal());
                        sb.append("</td>\n");
                        sb.append("  <td align=\"right\" style=\"padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; vertical-align: bottom; white-space: nowrap; font-size: 14pt; font-family: 微软雅黑; border-right-width: 1px; border-bottom-width: 1px; border-style: none solid solid none; border-right-color: white; border-bottom-color: white; background-color: rgb(242, 242, 242); background-position: initial initial; background-repeat: initial initial;\">");
                        sb.append(statistics.getNps());
                        sb.append("%</td></tr>");
                    }
                    sb.append("</tbody></table>");
                    sendMailVO.setContent(sb.toString());
                    sendMailVO.setFilename("NPS问卷统计-外部" + periodStr[0] + "~" + periodStr[1]);
                    sendMailVO.setSubject("NPS统计-上周【" + bu.getName() + "】");
                    sendMailVO.setToMail(bu.getValue());
                    sendMailVO.setInputStream(npsExcelInputStream);
                    //开启多个线程去发邮件
                    executos.execute(new Runnable() {
                        @Override
                        public void run() {
                            SendMailUtil.sendMail(sendMailVO);
                        }
                    });
                }
            }
            flag = true;
        } catch (Exception e) {
            log.error("sendNPSEmail error:" + e.getMessage(), e);
            flag = false;
        }
        log.info("sendNPSEmail end.");
        return flag;
    }
}
